package com.b2c.repository;

import com.b2c.entity.result.PagingResponse;
import com.b2c.entity.erp.ErpGoodsSpecEntity;
import com.b2c.entity.kwai.DcKwaiOrderEntity;
import com.b2c.entity.kwai.DcKwaiOrdersItemEntity;
import com.b2c.entity.result.EnumResultVo;
import com.b2c.entity.result.ResultVo;
import com.b2c.entity.enums.EnumErpOrderSendStatus;
import com.b2c.entity.enums.third.EnumKwaiOrderStatus;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


@Repository
public class DcKwaiOrderRepositroy {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> editKwaiOrder(DcKwaiOrderEntity order){
        Integer count = jdbcTemplate.queryForObject("select count(0) from dc_kwai_order where oid=?",Integer.class,order.getOid());
        if(count>0){
            jdbcTemplate.update("update dc_kwai_order set status=? where oid=?",order.getStatus(),order.getOid());
            return new ResultVo<>(EnumResultVo.DataExist,"已存在");
        }
        /*****1、添加order*****/
        StringBuilder orderInsertSQL = new StringBuilder();
        orderInsertSQL.append("INSERT INTO dc_kwai_order");
        orderInsertSQL.append(" SET ");
        orderInsertSQL.append(" oid=?,");
        orderInsertSQL.append(" createTime=?,");
        orderInsertSQL.append(" discountFee=?,");
        orderInsertSQL.append(" expressFee=?,");
        orderInsertSQL.append(" num=?,");
        orderInsertSQL.append(" consignee=?,");
        orderInsertSQL.append(" mobile=?,");
        orderInsertSQL.append(" payTime=?,");
        orderInsertSQL.append(" buyerRemark=?,");
        orderInsertSQL.append(" sellerRemark=?,");
        orderInsertSQL.append(" status=?,");
        orderInsertSQL.append(" refund=?,");
        orderInsertSQL.append(" totalFee=?,");
        orderInsertSQL.append(" province=?,");
        orderInsertSQL.append(" city=?,");
        orderInsertSQL.append(" district=?, ");
        orderInsertSQL.append(" address=?,");
        orderInsertSQL.append(" createOn=?");
        try {
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(orderInsertSQL.toString(), Statement.RETURN_GENERATED_KEYS);
                    ps.setLong(1, order.getOid());
                    ps.setLong(2, order.getCreatetime());
                    ps.setBigDecimal(3, order.getDiscountfee());
                    ps.setBigDecimal(4, order.getExpressfee());
                    ps.setInt(5, order.getNum());
                    ps.setString(6, order.getConsignee());
                    ps.setString(7, order.getMobile());
                    ps.setLong(8,order.getPaytime());
                    ps.setString(9,order.getBuyerRemark());
                    ps.setString(10,order.getSellerRemark());
                    ps.setInt(11,order.getStatus());
                    ps.setInt(12,order.getRefund());
                    ps.setBigDecimal(13,order.getTotalfee());
                    ps.setString(14,order.getProvince());
                    ps.setString(15,order.getCity());
                    ps.setString(16,order.getDistrict());
                    ps.setString(17,order.getAddress());
                    ps.setLong(18,System.currentTimeMillis() / 1000);
                    return ps;
                }
            }, keyHolder);
            Long orderId = keyHolder.getKey().longValue();
            /*****1、添加dc_douyin_orders_items*****/
            String itemSQL ="INSERT INTO dc_kwai_orders_item (orderId,itemId,erpGoodsId,erpGoodsSpecId,itemTitle,itemPicUrl,goodsNum,goodsSpec," +
                    "skuNick,price,num,refundId,refundStatus)" +
                    "VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?)";
            String erpGoodSpecSQL = "SELECT s.*,g.name as goodTitle,g.number as goodsNumber from "+Tables.ErpGoodsSpec+" s LEFT JOIN " + Tables.ErpGoods + " g ON g.id=s.goodsId where s.specNumber=?";
            for (var item:order.getItems()) {
                var specList=jdbcTemplate.query(erpGoodSpecSQL, new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), item.getSkunick());
                ErpGoodsSpecEntity spec=null;
                if(specList!=null && specList.size()>0) spec=specList.get(0);
                String goodsNumber=spec!=null ? spec.getGoodsNumber() : "";
                Integer goodsId=spec!=null ?  spec.getGoodsId() : 0;
                Integer specId=spec!=null ? spec.getId() : 0;
                jdbcTemplate.update(itemSQL,orderId,item.getItemid(),goodsId,specId,item.getItemtitle(),item.getItempicurl(),goodsNumber,item.getGoodsspec(),
                        item.getSkunick(),item.getPrice(),item.getNum(),item.getRefundId(),item.getRefundStatus());
            }
            return new ResultVo<>(EnumResultVo.SUCCESS,"成功");
        }catch (Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail,"异常："+e.getMessage());
        }
    }

    public PagingResponse<DcKwaiOrderEntity> getKwaiOrders(Integer pageIndex, Integer pageSize, String orderNum, Integer startTime, Integer endTime, String state) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS  A.* ");
        sb.append(" FROM dc_kwai_order A ");
        sb.append(" WHERE 1=1 ");
        List<Object> params = new ArrayList<>();

        if (!StringUtils.isEmpty(orderNum)) {
            sb.append("AND A.oid=? ");
            params.add(orderNum);
        }

        if (!StringUtils.isEmpty(startTime)) {
            sb.append("AND A.createTime > ? ");
            params.add(startTime);
        }
        if (!StringUtils.isEmpty(endTime)) {
            sb.append("AND A.createTime <= ? ");
            params.add(endTime);
        }
        if (!StringUtils.isEmpty(state)) {
            sb.append("AND  A.status = ?  ");
            params.add(state);
        }
        sb.append("ORDER BY A.oid DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        List<DcKwaiOrderEntity> lists = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(DcKwaiOrderEntity.class), params.toArray(new Object[params.size()]));
        int totalSize  = getTotalSize();
        lists.forEach(list->list.setItems(jdbcTemplate.query("select * from dc_kwai_orders_item where orderId=?",new BeanPropertyRowMapper<>(DcKwaiOrdersItemEntity.class),list.getId())));
        return new PagingResponse<>(pageIndex, pageSize,totalSize , lists);
    }

    public DcKwaiOrderEntity getOderDetailByOrderId(Long id){
        List<DcKwaiOrderEntity> lists = jdbcTemplate.query("select * from dc_kwai_order where id=?", new BeanPropertyRowMapper<>(DcKwaiOrderEntity.class),id);
        lists.forEach(list->list.setItems(jdbcTemplate.query("select * from dc_kwai_orders_item where orderId=?",new BeanPropertyRowMapper<>(DcKwaiOrdersItemEntity.class),list.getId())));
        return lists.get(0);
    }

    public ResultVo<Long> updOrderSpec(Long orderItemId, Integer erpGoodSpecId,Integer quantity){
        if(orderItemId==null || orderItemId.longValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，缺少orderItemId");
        }

        if(erpGoodSpecId==null || erpGoodSpecId.intValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，缺少erpGoodSpecId");
        }
        String sql = "SELECT * FROM dc_kwai_order WHERE id = (SELECT orderId FROM dc_kwai_orders_item WHERE id=? )";
        try {
            var order = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(DcKwaiOrderEntity.class),orderItemId);
            if(order.getAuditStatus().intValue()!=0)
                return new ResultVo<>(EnumResultVo.DataError,"订单已经确认，不允许修改");

            if(order.getSendStatus().intValue() != 0 )
                return new ResultVo<>(EnumResultVo.DataError,"订单不是待发货状态，不允许修改");

            var orderItem = jdbcTemplate.queryForObject("SELECT * FROM dc_kwai_orders_item WHERE id=?",new BeanPropertyRowMapper<>(DcKwaiOrdersItemEntity.class),orderItemId);

            /**************************/
            String erpGoodSpecSQL = "SELECT s.*,g.name as goodTitle,g.number as goodsNumber from "+Tables.ErpGoodsSpec+" s LEFT JOIN " + Tables.ErpGoods + " g ON g.id=s.goodsId WHERE s.id=?";
            var erpOrderSpec = jdbcTemplate.queryForObject(erpGoodSpecSQL,new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class),erpGoodSpecId);

            String remark = "修改商品，原sku："+orderItem.getErpgoodsId()+"("+orderItem.getSkunick()+")数量:"+orderItem.getNum()+"，新sku:"+erpOrderSpec.getId()+"("+erpOrderSpec.getSpecNumber()+")数量:"+quantity;

            jdbcTemplate.update("UPDATE dc_kwai_orders_item SET erpGoodsId=?,erpGoodsSpecId=?,skuNick=?,goodsNum=?,num=?,remark=? WHERE id=?"
                    ,erpOrderSpec.getGoodsId(),erpOrderSpec.getId(),erpOrderSpec.getSpecNumber(),erpOrderSpec.getGoodsNumber(),quantity,remark,orderItemId);

            return new ResultVo<>(EnumResultVo.SUCCESS,"成功");
        }catch (Exception e){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，不存在orderItem");
        }
    }
    /**
     * 快手订单发货
     * @param KwaiOid
     */
    public void updKwaiOrderStatus(Long KwaiOid){
        jdbcTemplate.update("UPDATE dc_kwai_order SET status=? WHERE oid=?", EnumKwaiOrderStatus.Delivered.getIndex(),KwaiOid);
    }

    /**
     *
     * @param orderId
     * @param erpGoodsId
     * @param erpGoodSpecId
     * @param quantity
     * @return
     */
    public ResultVo<Long> addOrderGift(Long orderId, Integer erpGoodsId, Integer erpGoodSpecId, Integer quantity){
        if(orderId==null || orderId.longValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，缺少orderId");
        }
        if(erpGoodsId==null || erpGoodsId.intValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，缺少erpGoodsId");
        }
        if(erpGoodSpecId==null || erpGoodSpecId.intValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，缺少erpGoodSpecId");
        }
        if(quantity==null || quantity.intValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，缺少quantity");
        }

        String sql = "SELECT * FROM dc_kwai_order WHERE id =? ";
        try {
            var order = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(DcKwaiOrderEntity.class),orderId);

            if(order.getAuditStatus().intValue()!=0)
                return new ResultVo<>(EnumResultVo.DataError,"订单已经确认，不允许添加赠品");
            if(order.getSendStatus().intValue() != EnumErpOrderSendStatus.WaitOut.getIndex() )
                return new ResultVo<>(EnumResultVo.DataError,"订单不是待发货状态，不允许添加赠品");


            /**************************/
            String erpGoodSpecSQL = "SELECT s.*,g.name as goodTitle,g.number as goodsNumber from "+Tables.ErpGoodsSpec+" s LEFT JOIN " + Tables.ErpGoods + " g ON g.id=s.goodsId WHERE s.id=?";
            var erpGoodsSpec = jdbcTemplate.queryForObject(erpGoodSpecSQL,new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class),erpGoodSpecId);

            String remark = "添加赠品"+erpGoodsSpec.getId()+"("+erpGoodsSpec.getSpecNumber()+")数量："+quantity;
            String specName = "";
            if(StringUtils.isEmpty(erpGoodsSpec.getColorValue())==false){
                specName += "颜色："+erpGoodsSpec.getColorValue();
            }
            if(StringUtils.isEmpty(erpGoodsSpec.getSizeValue())==false){
                specName += "尺码："+erpGoodsSpec.getSizeValue();
            }
            jdbcTemplate.update("INSERT INTO  dc_kwai_orders_item SET itemId=0,refundId=0,refundStatus=0,orderId=?," +
                            "erpGoodsId=?,erpGoodsSpecId=?,itemTitle=?,itemPicUrl=?,goodsNum=?,goodsSpec=?,skuNick=?,price=0,num=?,remark=?,isGift=1"
                    ,orderId,erpGoodsSpec.getGoodsId(),erpGoodsSpec.getId(),erpGoodsSpec.getGoodTitle(),erpGoodsSpec.getColorImage()
                    ,erpGoodsSpec.getGoodsNumber(),specName,erpGoodsSpec.getSpecNumber(),quantity,remark);
            return new ResultVo<>(EnumResultVo.SUCCESS,"成功");
        }catch (Exception e){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，不存在order");
        }
    }
}
